package com.gzhryc.jdbc.dialect.mysql;

import com.gzhryc.jdbc.dialect.AbsDbHelper;
import com.gzhryc.jdbc.log.Logger;
import com.gzhryc.jdbc.models.Db;
import com.gzhryc.jdbc.models.DbColumn;
import com.gzhryc.jdbc.models.DbTable;
import com.gzhryc.jdbc.models.IndexInfo;

import java.sql.*;
import java.util.Date;
import java.util.*;

public class MysqlDbHelper extends AbsDbHelper{
	
	static Logger log = Logger.getLogger(MysqlDbHelper.class);

	public MysqlDbHelper(String connectionKey) {
		super(connectionKey);
	}

	public List<Db> findAllDb() throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			List<Db> result =  new ArrayList<Db>();
			conn = getManageConn();
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM SCHEMATA");
			while(rs.next()){
				Db db = new Db();
				db.setName(rs.getString("SCHEMA_NAME"));
				result.add(db);
			}
			return result;
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
	}

	public List<DbTable> findAllDbTable(String database) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			List<DbTable> result = new ArrayList<DbTable>();
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM TABLES WHERE TABLE_SCHEMA = ?");
			pstmt.setString(1, database);
			rs = pstmt.executeQuery();
			while(rs.next()){
				DbTable table = new DbTable();
				table.setDbName(rs.getString("TABLE_SCHEMA"));
				table.setTableName(rs.getString("TABLE_NAME"));
				table.setComment(rs.getString("TABLE_COMMENT"));
				java.sql.Date createTime = rs.getDate("CREATE_TIME");
				if(createTime != null){
					table.setCreateDatetime(new Date(createTime.getTime()));
				}
				result.add(table);
			}
			return result;
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
	}

	public List<IndexInfo> findAllIndex(String tableName) throws SQLException{
		Connection conn = null;
		ResultSet rs = null;
		try{
			Map<String,IndexInfo> map = new HashMap<String,IndexInfo>();
			conn = getConn();
			PreparedStatement pstmt = conn.prepareStatement("SHOW INDEX FROM " + tableName);
			rs = pstmt.executeQuery();
			while(rs.next()){
				String keyName = rs.getString("Key_name");	//索引的名称
				IndexInfo info = map.get(keyName);
				if(info == null) {
					info = new IndexInfo();
					map.put(keyName, info);
				}
				info.setKeyName(keyName);
				info.setNonUnique(rs.getInt("Non_unique"));		//如果索引不能包括重复词，则为0。如果可以，则为1
				int index = rs.getInt("Seq_in_index");			//索引中的列序列号，从1开始
				info.getColumnNames().add((index-1),rs.getString("Column_name"));	//列名称
				//rs.getString("Collation");	//列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）
				//rs.getInt("Cardinality"));	//索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，
												//所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大
				//rs.getInt("Sub_part"));		//如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
				//rs.getString("Packed"));		/指示关键字如何被压缩。如果没有被压缩，则为NULL。
				//rs.getBoolean("Null"));		//如果列含有NULL，则含有YES。如果没有，则该列含有NO
				//rs.getString("Index_type"));	//用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
			}
			if(map.size() > 0) {
				return new ArrayList<>(map.values());
			}
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return null;
	}

	public List<String> findAllUniqueKeyName(String database,String tableName) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try {
			List<String> result = new ArrayList<>();
			conn = getManageConn();
			String sql = "SELECT DISTINCT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS" +
					" WHERE CONSTRAINT_SCHEMA = ? AND CONSTRAINT_TYPE = 'UNIQUE' AND TABLE_NAME = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, database);
			pstmt.setString(2, tableName);
			log.sql(connectionKey, sql + "; " + database + "," + tableName);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result.add(rs.getString(1));
			}
			return result;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	public boolean existTable(String database,String tableName) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			conn = getManageConn();
			String sql = "SELECT COUNT(1) FROM TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, database);
			pstmt.setString(2, tableName);
			log.sql(connectionKey, sql + "; " + database + "," + tableName);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1) > 0;
			}
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return false;
	}
	
	public boolean existView(String database,String viewName) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("SELECT COUNT(1) FROM VIEWS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
			pstmt.setString(1, database);
			pstmt.setString(2, viewName);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1) > 0;
			}
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return false;
	}

	public List<DbColumn> findAllDbColumn(String database, String tableName)
			throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			List<DbColumn> result = new ArrayList<DbColumn>();
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
			pstmt.setString(1, database);
			pstmt.setString(2, tableName);
			rs = pstmt.executeQuery();
			while(rs.next()){
				DbColumn column = new DbColumn();
				column.setDbName(rs.getString("TABLE_SCHEMA"));
				column.setTableName(rs.getString("TABLE_NAME"));
				column.setName(rs.getString("COLUMN_NAME"));
				column.setType(rs.getString("DATA_TYPE"));
				column.setCharMaxLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
				column.setColumnKey(rs.getString("COLUMN_KEY"));
				column.setDefaultValue(rs.getObject("COLUMN_DEFAULT"));
				column.setComment(rs.getString("COLUMN_COMMENT"));
				column.setIsNullable(rs.getBoolean("IS_NULLABLE"));
				result.add(column);
			}
			return result;
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
	}

	public boolean existDb(String database) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		try{
			log.info("检查数据库是否存在！");
			conn = getManageConn();
			PreparedStatement pstmt = conn.prepareStatement("SELECT COUNT(*) FROM SCHEMATA WHERE SCHEMA_NAME = ?");
			pstmt.setString(1, database);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1) > 0;
			}
		}finally{
			if(rs != null){
				rs.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		return false;
	}

}
